using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Windows.Forms;
using Atechnology.DBConnections2;
using Atechnology.winDraw.Model;
using Atechnology.winDraw;
using Atechnology.Components;
using Atechnology.ecad.Document.Classes;
using Atechnology.ecad.Document.DataSets;
namespace Atechnology.ecad.Calc{
public class RunCalc{
public DBConnections2.dbconn db;
public DataTable table_docoper;
public DataTable table_optimdoc;
public DataTable table_storedocgroup;
public DataTable table_docrelation;
public DataTable table_storedoc;
public DataTable table_storedocpos;
public DataTable table_equipmentdoc;
public DataTable table_temp;
public DataSet ds;
public DataRow dr1;
public ManufactDocClass mdc;
public int err;
#region константы типов документов
public int iddocoperProf=130; // Профиль
public int iddocoperFurn=131; // Фурнитура
public int iddocoperMS=132; // Москитки
public int iddocoperPod=133; // Подоконники
public int iddocoperOtl=134; // Отливы
public int iddocoperSoed=135; // Доборный профиль
public int iddocoperKor=170; // Коробочки
#endregion константы типов документов
public string parentid = "";
public int NewIdStoreDocGroup =90;
public int idstoredoc_rn = 0;
public string name_rn = "";
public int idpeople_rn = 0;
public DateTime dtdoc_rn;
public int idstoredocgroup_rn;
public int idstoredepart1_rn;
public int idmanufactdoc;
public int idmanufactdocoper;
public DateTime dtdoc;
public string CommStr = "";
public List<int> StoreList;
public RunCalc(){}
//Стартовый метод
public void Run(DBConnections2.dbconn _db, DataRow [] _dr){
try{
err = 0;
db = _db;
DataRow [] dr = _dr;
dr1 = _dr[0];
ds = _dr[0].Table.DataSet;
err = 11;
Atechnology.ecad.Calc.SystemScript.RunCalc.Errors.Clear();
err = 12;
idmanufactdoc = Convert.ToInt32(dr[0]["idmanufactdoc"]);
err = 14;
idmanufactdocoper = Convert.ToInt32(dr[0]["iddocoper"]);
err = 15;
dtdoc = Convert.ToDateTime(dr[0]["dtdoc"]);
//mdc = new ManufactDocClass(db, idmanufactdoc);
//mdc.LoadDoc(idmanufactdoc);
//mdc.SaveToBase();
err = 1;
LoadData();
if (table_equipmentdoc.Rows.Count == 0){
MessageBox.Show("Перед формированием накладных необходимо сформировать файлы для оборудования");
return;
}
StoreList = new List<int>();
err = 2;
#region проверка и создание папок
string year = dtdoc.Year.ToString();
string month = dtdoc.Month.ToString();
switch (Convert.ToInt32(dtdoc.Month)){
case 1:
month = "январь";
break;
case 2:
month = "февраль";
break;
case 3:
month = "март";
break;
case 4:
month = "апрель";
break;
case 5:
month = "май";
break;
case 6:
month = "июнь";
break;
case 7:
month = "июль";
break;
case 8:
month = "август";
break;
case 9:
month = "сентябрь";
break;
case 10:
month = "октябрь";
break;
case 11:
month = "ноябрь";
break;
case 12:
month = "декабрь";
break;
}
string CommStr = "";
DataRow [] dt = table_storedocgroup.Select("deleted is null and parentid is null and name = '"+year+"'");
if (dt.Length == 0){
// тут мы должны будем создать новую папку год. Не забыть присвоить dt
// не забыть права
NewIdStoreDocGroup = Convert.ToInt32(Atechnology.DBConnections2.dbconn.GetGenId("gen_storedocgroup", 1));
CommStr = "insert into storedocgroup";
CommStr += " (idstoredocgroup, name, parentid) ";
CommStr += "values ( "+ NewIdStoreDocGroup.ToString()+", '"+year +"', null)";
db.OpenDB();
db.command.CommandText = CommStr;
db.command.ExecuteNonQuery();
db.CloseDB();
parentid = NewIdStoreDocGroup.ToString();
}else{
parentid = dt[0]["idstoredocgroup"].ToString();
}
// string parentid = dt[0]["parentid"].ToString();
DataRow [] dtmont = table_storedocgroup.Select("deleted is null and parentid = "+parentid +" and name = '"+month+"'");
if (dtmont.Length == 0){
// тут мы должны будем создать новую папку месяц в папке год. Не забыть присвоить dtmont
// не забыть права
NewIdStoreDocGroup = Convert.ToInt32(Atechnology.DBConnections2.dbconn.GetGenId("gen_storedocgroup", 1));
CommStr = "insert into storedocgroup";
CommStr += " (idstoredocgroup, name, parentid) ";
CommStr += "values ( "+ NewIdStoreDocGroup.ToString()+", '"+month +"', "+parentid + ")";
db.OpenDB();
db.command.CommandText = CommStr;
db.command.ExecuteNonQuery();
db.CloseDB();
parentid = NewIdStoreDocGroup.ToString();
}else{
parentid = dtmont[0]["idstoredocgroup"].ToString();
}
// MessageBox.Show(parentid);
string name_md = dr[0]["name"].ToString()+" "+dr[0]["comment"].ToString();
DataRow [] dtmd = table_storedocgroup.Select("deleted is null and parentid = "+parentid +" and name = '"+name_md+"'");
if (Convert.ToInt32(dtmd.Length) == 0){
// тут мы должны будем создать новую папку производственное задание в папке месяц. Не забыть присвоить dtmont
// не забыть права
NewIdStoreDocGroup = Convert.ToInt32(Atechnology.DBConnections2.dbconn.GetGenId("gen_storedocgroup", 1));
CommStr = "insert into storedocgroup";
CommStr += " (idstoredocgroup, name, parentid) ";
CommStr += "values ( "+ NewIdStoreDocGroup.ToString()+", '"+name_md +"', "+parentid + ")";
db.OpenDB();
db.command.CommandText = CommStr;
db.command.ExecuteNonQuery();
db.CloseDB();
} else {
NewIdStoreDocGroup = Convert.ToInt32(dtmd[0]["idstoredocgroup"]);
}
// к этому моменту у нас должно быть создана папка год\месяц\Произ задание
#endregion проверка и создание папок
err = 3;
// #region провекра на дублирование
//
// // сначала проверим общую расходную накладную и вообще-то, по идее, можно сделать это в цикле....
// DataRow [] docrel1 = table_docrelation.Select("deleted is null and idparentdoc = " +idmanufactdoc.ToString());
// // MessageBox.Show(idmanufactdoc.ToString()+" впереди idmanufact "+docrel.Length.ToString() );
//
// int err1 =0;
// DataRow [] ddo = table_docoper.Select("deleted is null and iddocappearance=5 and storetyp=1 and iddocoper not in (9,10,128)");
// //MessageBox.Show(ddo.Length.ToString());
// int ii = 0;
// foreach(DataRow dd1 in ddo){
// ii++;
//
// if (TestDubl(Convert.ToInt32(dd1["iddocoper"]), docrel1) == false){
// //return;
// }
// }
// #endregion провекра на дублирование
err = 4;
#region проверка и создание расходных накладных
int numpos = 0;
// DataRow [] ddo1 = table_docoper.Select("deleted is null and iddocappearance=5 and iddocoper=63");
DataRow [] ddo1 = table_docoper.Select("deleted is null and iddocappearance=5 and storetyp=1 and iddocoper not in (9,10,128)");
DataRow [] docrel = table_docrelation.Select("deleted is null and idparentdoc = " +idmanufactdoc.ToString());
// DataRow [] ddo1 = table_docoper.Select("");
// MessageBox.Show(ddo1.Length.ToString()+" это длина docoper"+docrel.Length.ToString() );
foreach(DataRow dd1 in ddo1){
if (TestDubl(Convert.ToInt32(dd1["iddocoper"]), docrel) == false){
continue;
}else{
// создаем новую расходную накладную
err = 6;
string CommSpDoc = "";
//name_rn = ddo1[0]["prefix"].ToString()+ (Convert.ToInt32(ddo1[0]["numpos"])+1).ToString()+ddo1[0]["suffix"].ToString();
//name_rn = dd1["prefix"].ToString()+ (Convert.ToInt32(dd1["numpos"])+1).ToString()+dd1["suffix"].ToString();
name_rn = dr1["name"].ToString();
idpeople_rn = Atechnology.ecad.Settings.idpeople;
idstoredocgroup_rn = NewIdStoreDocGroup;
idstoredepart1_rn = 2;
string CommStrTable = "";
// и только теперь мы дошли до того, чтобы загрузить собственно заказ. Бд.
// public int iddocoperProf=130; // Профиль
// public int iddocoperFurn=131; // Фурнитура
// public int iddocoperMS=132; // Москитки
// public int iddocoperPod=133; // Подоконники
// public int iddocoperOtl=134; // Отливы
// public int iddocoperSoed=135; // Доборный профиль
// public int iddocoperKor=170; // Коробочки
err = 7;
switch(Convert.ToInt32(dd1["iddocoper"])){
case 130: // Профиль
if (Convert.ToInt32(dr1["iddocoper"]) == 8){ // Стандарт(задание)
CommStrTable = @"select
mc.idgood,
sum(case when m.typ =1
then mc.qustore*mdp.qu
else mc.qu*mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(mc.height) end) as height,
(case when m.typ =1
then null
else sum(mc.width) end) as width,
(case when m.typ = 1
then 1000
else sum(mc.thick) end) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.deleted is null
join good g on g.idgood = mc.idgood
join measure m on m.idmeasure = g.idmeasure
where mc.deleted is null
and g.idgoodtype in (2, 11, 16, 25, 27)
and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by mc.idgood, m.typ
union all
select ep.idgood,
count(ep.idequipmentprofilein)*2 as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000)*2 as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from view_modelcalc
where deleted is null and idorderitem = ep.idorderitem and good_marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where ep.profiletype in ('RamaItem', 'StvorkaItem', 'Shtapik')
and ep.profilemarking not like '140028%' and ep.profilemarking not like '554017%'
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick
union all
select ep.idgood,
count(ep.idequipmentprofilein) as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000) as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from view_modelcalc
where deleted is null and idorderitem = ep.idorderitem and good_marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where (ep.profiletype in ('StvorkaItem') and (ep.profilemarking like '140028%' or ep.profilemarking like '554017%') or ep.profiletype in ('Impost'))
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick";
}else { // Не стандарт(задание)
CommStrTable = @"select
mc.idgood,
sum(case when m.typ =1
then mc.qustore*mdp.qu
else mc.qu*mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(mc.height) end) as height,
(case when m.typ =1
then null
else sum(mc.width) end) as width,
(case when m.typ = 1
then 1000
else sum(mc.thick) end) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
join good g on g.idgood = mc.idgood
join measure m on m.idmeasure = g.idmeasure
where mc.deleted is null
and g.idgoodtype in (1, 2, 11, 15, 16, 25, 27) and m.typ = 1
and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by mc.idgood, m.typ";
}
idstoredepart1_rn = 2;
break;
case 131: // Фурнитура
CommStrTable = @"select
mc.idgood,
sum(mc.qu*mdp.qu) as qu,
sum(mc.height) as height,
sum(mc.width) as width,
sum(mc.thick) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
join good g on g.idgood = mc.idgood
where mc.deleted is null and isnull(mc.addstr4, '') <> 'MC'
and g.idgoodtype in (3, 4, 22)
and mdp.idmanufactdoc = " + idmanufactdoc.ToString()+@" group by mc.idgood";
idstoredepart1_rn = 4;
break;
case 170: // коробочка
CommStrTable = @"
select goods.idgood, sum(goods.qu) qu, null as height, null as width, null as thick, null as modelpart, null as price, sum(goods.qu) as qustore, null as weight, null as idorderitem
from
(select g.idgood, oi.qu
from
(select distinct oi.idorder from manufactdocpos mdp
join orderitem oi on oi.idorderitem = mdp.idorderitem and mdp.idgood is null
where mdp.deleted is null and mdp.idgood is null and mdp.idmanufactdoc = " + idmanufactdoc.ToString()+@" ) ord
join orderitem oi on oi.idorder = ord.idorder and oi.deleted is null and oi.idmodel is null
join good g on g.idgood = oi.idgood and g.idgoodtype in (12,17,34,35,48,51,52,60)
union all
select g.idgood, idp.qu
from (select distinct oi.idorder from manufactdocpos mdp
join orderitem oi on oi.idorderitem = mdp.idorderitem and mdp.idgood is null
where mdp.deleted is null and mdp.idgood is null and mdp.idmanufactdoc = " + idmanufactdoc.ToString()+@" ) ord
join installdoc id on id.idorder = ord.idorder and id.deleted is null
join installdocpos idp on idp.idinstalldoc = id.idinstalldoc and idp.deleted is null
join good g on g.idgood = idp.idgood and (g.idgoodgroup in (2840,2841) or g.idgoodtype in (60,17))
union all
select g.idgood, (mdp.qu*mc.qu) qu
from manufactdocpos mdp
join modelcalc mc on mc.idorderitem = mdp.idorderitem and mc.deleted is null and mdp.idgood is null
join good g on g.idgood = mc.idgood and g.idgoodtype in (12,17,34,35,48,51,52,60)
where mdp.deleted is null and mdp.idgood is null and mdp.idmanufactdoc = " + idmanufactdoc.ToString()+@"
union all
select g.idgood, (mdp.qu*og.qu) qu
from manufactdocpos mdp
join ordergood og on og.idorderitem = mdp.idorderitem and og.deleted is null
join orderitem oi on oi.idorderitem = mdp.idorderitem
join good g on g.idgood = og.idgood and g.idgoodtype in (12,17,34,35,48,51,52,60)
where mdp.deleted is null and mdp.idgood is null and mdp.idmanufactdoc = " + idmanufactdoc.ToString()+@" ) goods
group by goods.idgood";
idstoredepart1_rn = 4;
break;
case 132: //Москитки
#region По спецификации
CommStrTable = @"select
mc.idgood,
sum(case when m.typ =1
then mc.qustore*mdp.qu
else mc.qu*mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(mc.height) end) as height,
(case when m.typ =1
then null
else sum(mc.width) end) as width,
(case when m.typ = 1
then 1000
else sum(mc.thick) end) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
join good g on g.idgood = mc.idgood and g.idgoodtype in (10, 33)
join measure m on m.idmeasure = g.idmeasure
where mc.deleted is null and (isnull(mc.addstr4, '') <> 'ПрофильМС' or mc.addstr4 = 'МС')
and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by mc.idgood, m.typ
union all
select
mc.idgood,
sum(case when m.typ =1
then mc.qustore*mdp.qu
else mc.qu*mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(mc.height) end) as height,
(case when m.typ =1
then null
else sum(mc.width) end) as width,
(case when m.typ = 1
then 1000
else sum(mc.thick) end) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
join good g on g.idgood = mc.idgood and g.idgoodtype in (3)
join measure m on m.idmeasure = g.idmeasure
where mc.deleted is null and mc.addstr4 = 'МС'
and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by mc.idgood, m.typ
union all
select distinct
oi.idgood,
sum(case when m.typ =1
then oi.qustore
else oi.qu end) as qu,
(case when m.typ =1
then null
else sum(oi.height) end) as height,
(case when m.typ =1
then null
else sum(oi.width) end) as width,
(case when m.typ = 1
then 1000
else sum(oi.thick) end) as thick,
'Допы',
max(oi.price) price,
sum(oi.qustore) as qustore,
sum(oi.weight) as weight,
min(oi.idorderitem) as idorderitem
from orderitem oi
join orderitem oii on oii.idorder = oi.idorder and oi.deleted is null and oi.idgood is not null
join manufactdocpos mdp on mdp.idorderitem = oii.idorderitem and mdp.deleted is null
join good g on g.idgood = oi.idgood and g.idgoodtype in (10, 33)
join measure m on m.idmeasure = g.idmeasure
where mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by oi.idgood, m.typ
union all
select
og.idgood,
sum(case when m.typ =1
then og.qustore*mdp.qu
else og.qu*mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(og.height) end) as height,
(case when m.typ =1
then null
else sum(og.width) end) as width,
(case when m.typ = 1
then 1000
else sum(og.thick) end) as thick,
'Допы',
max(og.price) price,
sum(og.qustore*mdp.qu) as qustore,
sum(og.weight) as weight,
min(og.idorderitem) as idorderitem
from ordergood og
join manufactdocpos mdp on mdp.idorderitem = og.idorderitem and mdp.deleted is null and mdp.idgood is null
join good g on g.idgood = og.idgood and g.idgoodtype in (10, 33)
join measure m on m.idmeasure = g.idmeasure
where og.deleted is null and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by og.idgood, m.typ";
#endregion
#region По оптимизации
CommStrTable += @" union all
select ep.idgood,
count(ep.idequipmentprofilein)*2 as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000)*2 as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from view_modelcalc
where deleted is null and idorderitem = ep.idorderitem and good_marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where ep.profiletype in ('Moskit') and ep.profilemarking like '6211%'
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick
union all
select ep.idgood,
count(ep.idequipmentprofilein) as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000) as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from view_modelcalc
where deleted is null and idorderitem = ep.idorderitem and good_marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where ep.profiletype in ('Moskit') and ep.profilemarking like '6212%'
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick";
#endregion
idstoredepart1_rn = 2;
break;
case 133: // Подоконник
#region По спецификации
// CommStrTable = @"select
// mc.idgood,
// sum(mc.qu*mdp.qu) as qu,
// sum(mc.height) as height,
// sum(mc.width) as width,
// sum(mc.thick) as thick,
// null as modelpart,
// max(mc.price) price,
// sum(mc.qustore*mdp.qu) as qustore,
// sum(mc.weight) as weight,
// min(mc.idorderitem) as idorderitem
// from modelcalc mc
// join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
// join good g on g.idgood = mc.idgood and g.idgoodtype in (13)
// where mc.deleted is null
// and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@"
// group by mc.idgood
//
// union all
//
// select distinct
// oi.idgood,
// sum(oi.qu) as qu,
// sum(oi.height) as height,
// sum(oi.width) as width,
// sum(oi.thick) as thick,
// 'Допы',
// max(oi.price) price,
// sum(oi.qustore) as qustore,
// sum(oi.weight) as weight,
// min(oi.idorderitem) as idorderitem
// from orderitem oi
// join orderitem oii on oii.idorder = oi.idorder and oi.deleted is null and oi.idgood is not null
// join manufactdocpos mdp on mdp.idorderitem = oii.idorderitem and mdp.deleted is null
// join good g on g.idgood = oi.idgood and g.idgoodtype in (13)
// where mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@"
// group by oi.idgood
//
// union all
//
// select
// og.idgood,
// sum(og.qu*mdp.qu) as qu,
// sum(og.height) as height,
// sum(og.width) as width,
// sum(og.thick) as thick,
// 'Допы',
// max(og.price) price,
// sum(og.qustore*mdp.qu) as qustore,
// sum(og.weight) as weight,
// min(og.idorderitem) as idorderitem
// from ordergood og
// join manufactdocpos mdp on mdp.idorderitem = og.idorderitem and mdp.deleted is null and mdp.idgood is null
// join good g on g.idgood = og.idgood and g.idgoodtype in (13)
// where og.deleted is null and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+
// " group by og.idgood";
#endregion
#region По оптимизации
CommStrTable = @"
select ep.idgood,
count(ep.idequipmentprofilein) as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000) as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from good
where marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where ep.profiletype in ('Podokonnik')
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick";
#endregion
idstoredepart1_rn = 3;
break;
case 134: // Отливы
#region По спецификации
// CommStrTable = @"select
// mc.idgood,
// sum(mc.qu*mdp.qu) as qu,
// sum(mc.height) as height,
// sum(mc.width) as width,
// sum(mc.thick) as thick,
// null as modelpart,
// max(mc.price) price,
// sum(mc.qustore*mdp.qu) as qustore,
// sum(mc.weight) as weight,
// min(mc.idorderitem) as idorderitem
// from modelcalc mc
// join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.deleted is null and mdp.idgood is null
// join good g on g.idgood = mc.idgood and g.idgoodtype in (14)
// where mc.deleted is null
// and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@"
// group by mc.idgood
//
// union all
//
// select distinct
// oi.idgood,
// sum(oi.qu) as qu,
// sum(oi.height) as height,
// sum(oi.width) as width,
// sum(oi.thick) as thick,
// 'Допы',
// max(oi.price) price,
// sum(oi.qustore) as qustore,
// sum(oi.weight) as weight,
// min(oi.idorderitem) as idorderitem
// from orderitem oi
// join orderitem oii on oii.idorder = oi.idorder and oi.deleted is null and oi.idgood is not null
// join manufactdocpos mdp on mdp.idorderitem = oii.idorderitem and mdp.deleted is null
// join good g on g.idgood = oi.idgood and g.idgoodtype in (14)
// where mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@"
// group by oi.idgood
//
// union all
//
// select
// og.idgood,
// sum(og.qu*mdp.qu) as qu,
// sum(og.height) as height,
// sum(og.width) as width,
// sum(og.thick) as thick,
// 'Допы',
// max(og.price) price,
// sum(og.qustore*mdp.qu) as qustore,
// sum(og.weight) as weight,
// min(og.idorderitem) as idorderitem
// from ordergood og
// join manufactdocpos mdp on mdp.idorderitem = og.idorderitem and mdp.deleted is null and mdp.idgood is null
// join good g on g.idgood = og.idgood and g.idgoodtype in (14)
// where og.deleted is null and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+
// "group by og.idgood";
#endregion
#region По оптимизации
CommStrTable = @"
select ep.idgood,
count(ep.idequipmentprofilein) as qu,
null as height,
null as width,
ep.thick,
null as modelpart,
max(ep.pricebase) price,
sum(cast(ep.thick as numeric(15,4))/1000) as qustore,
null as weight,
null as idorderitem
from (select distinct
mc.idgood as idgood,
ep.idequipmentprofilein,
ep.whip as thick,
mc.pricebase
from equipmentprofile ep
join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
outer apply (select top 1 idgood, price2 pricebase from good
where marking = replace(ep.profilemarking,'_R','')) mc
join orderitem oi on oi.idorderitem = ep.idorderitem
join orders o on o.idorder = oi.idorder
where ep.profiletype in ('Otliv')
and epin.profilemarking not like '%_dublicate%'
and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
group by ep.idgood, ep.thick";
#endregion
idstoredepart1_rn = 8;
break;
case 135: // Доборные профили
#region По спецификации
CommStrTable = @"select
mc.idgood,
sum(case when m.typ =1
then mc.qustore/mc.qu*mdp.qu
else mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(mc.height) end) as height,
(case when m.typ =1
then null
else sum(mc.width) end) as width,
(case when m.typ = 1
then 1000
else sum(mc.thick) end) as thick,
null as modelpart,
max(mc.price) price,
sum(mc.qustore/mc.qu*mdp.qu) as qustore,
sum(mc.weight) as weight,
min(mc.idorderitem) as idorderitem
from modelcalc mc
join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem and mdp.idmodelcalc = mc.idmodelcalc and mdp.deleted is null and mdp.idgood is not null
join good g on g.idgood = mc.idgood and g.idgoodtype in (6, 26, 37, 44,19)
join measure m on m.idmeasure = g.idmeasure
where mc.deleted is null and isnull(mc.addstr4, '') <> 'Подставочный профиль'
and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by mc.idgood, m.typ
union all
select distinct
oi.idgood,
sum(case when m.typ =1
then oi.qustore/oi.qu*mdp.qu
else mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(oi.height) end) as height,
(case when m.typ =1
then null
else sum(oi.width) end) as width,
(case when m.typ = 1
then 1000
else sum(oi.thick) end) as thick,
'Допы',
max(oi.price) price,
sum(oi.qustore/oi.qu*mdp.qu) as qustore,
sum(oi.weight) as weight,
min(oi.idorderitem) as idorderitem
from orderitem oi
join manufactdocpos mdp on mdp.idorderitem = oi.idorderitem and mdp.deleted is null and mdp.idgood is not null and mdp.idordergood is null and mdp.idmodelcalc is null
join good g on g.idgood = oi.idgood and g.idgoodtype in (6, 26, 37, 44,19)
join measure m on m.idmeasure = g.idmeasure
where mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by oi.idgood, m.typ
union all
select
og.idgood,
sum(case when m.typ =1
then og.qustore/og.qu*mdp.qu
else mdp.qu end) as qu,
(case when m.typ =1
then null
else sum(og.height) end) as height,
(case when m.typ =1
then null
else sum(og.width) end) as width,
(case when m.typ = 1
then 1000
else sum(og.thick) end) as thick,
'Допы',
max(og.price) price,
sum(og.qustore/og.qu*mdp.qu) as qustore,
sum(og.weight) as weight,
min(og.idorderitem) as idorderitem
from ordergood og
join manufactdocpos mdp on mdp.idorderitem = og.idorderitem and mdp.idordergood = og.idordergood and mdp.deleted is null and mdp.idgood is not null
join good g on g.idgood = og.idgood and g.idgoodtype in (6, 26, 37, 44,19)
join measure m on m.idmeasure = g.idmeasure
where og.deleted is null and mdp.idmanufactdoc = "+idmanufactdoc.ToString()+@" group by og.idgood, m.typ";
#endregion
#region По оптимизации
// CommStrTable = @"
// select ep.idgood,
// count(ep.idequipmentprofilein) as qu,
// null as height,
// null as width,
// ep.thick,
// null as modelpart,
// max(ep.pricebase) price,
// sum(cast(ep.thick as numeric(15,4))/1000) as qustore,
// null as weight,
// null as idorderitem
// from (select distinct
// mc.idgood as idgood,
// ep.idequipmentprofilein,
// ep.whip as thick,
// mc.pricebase
// from equipmentprofile ep
// join equipmentprofileout epin on epin.idequipmentprofilein = ep.idequipmentprofilein and epin.whiptype in (0,1)
// outer apply (select top 1 idgood, price2 pricebase from view_modelcalc
// where deleted is null and idorderitem = ep.idorderitem and good_marking = replace(ep.profilemarking,'_R','')) mc
// join orderitem oi on oi.idorderitem = ep.idorderitem
// join orders o on o.idorder = oi.idorder
// where ep.profiletype in ('Podstavochnik','')
// and epin.profilemarking not like '%_dublicate%'
// and ep.idmanufactdoc = " + idmanufactdoc.ToString()+@") ep
// group by ep.idgood, ep.thick";
#endregion
idstoredepart1_rn = 2;
break;
}
err = Convert.ToInt32(dd1["iddocoper"]);
db.OpenDB();
db.command.CommandText = CommStrTable;
DataTable dt_my = new DataTable();
dt_my.Clear();
db.adapter.Fill(dt_my);
DataRow [] st = dt_my.Select();
if (st.Length == 0)
continue;
idstoredoc_rn = Convert.ToInt32(Atechnology.DBConnections2.dbconn.GetGenId("gen_storedoc", 1));
StoreList.Add(idstoredoc_rn);
CommSpDoc+= " update docoper set numpos = "+(Convert.ToInt32(dd1["numpos"])+1).ToString()+" where iddocoper = "+dd1["iddocoper"].ToString();
CommSpDoc += " insert into storedoc (idstoredoc, name, idpeople, dtdoc, idstoredocgroup, idstoredepart1, iddocoper) ";
CommSpDoc += " values ("+idstoredoc_rn.ToString()+" , '"+name_rn+"', "+idpeople_rn.ToString() +", convert(datetime, '"+System.DateTime.Today.ToShortDateString()+"' , 104), "+idstoredocgroup_rn.ToString()+", "+idstoredepart1_rn.ToString()+", "+dd1["iddocoper"].ToString()+")";
db.OpenDB();
db.command.CommandText = CommSpDoc;
db.command.ExecuteNonQuery();
db.CloseDB();
// это мы создали расходную накладную...
err = 9;
// теперь нам анадо прописать ее в docrelation
string CommDocRel = "";
// db.command.CommandText ="select isnull(max(iddocrelation),0) + 1 from docrelation";
// db.OpenDB();
// int iddocrelation = Convert.ToInt32(db.command.ExecuteScalar()) ;
int iddocrelation = Atechnology.DBConnections2.dbconn.GetGenId("gen_docrelation", 1);
db.CloseDB();
err = 95;
CommDocRel = " insert into docrelation (iddocrelation, idparentdoc, iddocappearanceparent, idchilddoc, iddocappearancechild ) ";
CommDocRel += " values ("+iddocrelation.ToString()+", "+idmanufactdoc.ToString()+", 2, "+ idstoredoc_rn.ToString()+", 5)";
db.OpenDB();
db.command.CommandText = CommDocRel;
db.command.ExecuteNonQuery();
err = 96;
err = 10;
foreach (DataRow std in st){
numpos++;
string CommStrStore = "";
CommStrStore = @" insert into storedocpos ( idstoredocpos, idstoredoc, idgood, qu, height, width, thick, numpos, part, price, qustore, weight, idorderitem )
values ( @idstoredocpos, @idstoredoc, @idgood, @qu, @height, @width, @thick, @numpos, @part, @price, @qustore, @weight, @idorderitem)";
err = 11;
double waste = 0;
try{
waste = Useful.GetDouble(Atechnology.ecad.Dictionary.Goods.table_good.Select("idgood = "+Convert.ToString(std["idgood"]))[0]["waste"]);
}catch{
waste = 0;
}
waste =0;
db.OpenDB();
db.command.CommandText = CommStrStore;
db.command.Parameters.Clear();
db.command.Parameters.Add("@idstoredocpos", System.Data.SqlDbType.Int).Value = Atechnology.DBConnections2.dbconn.GetGenId("gen_storedocpos", 1);
db.command.Parameters.Add("@idstoredoc", System.Data.SqlDbType.Int).Value = idstoredoc_rn;
db.command.Parameters.Add("@idgood", System.Data.SqlDbType.Int).Value = std["idgood"];
db.command.Parameters.Add("@qu", System.Data.SqlDbType.Decimal).Value = std["qu"];
db.command.Parameters.Add("@height", System.Data.SqlDbType.Int).Value = std["height"];
db.command.Parameters.Add("@width", System.Data.SqlDbType.Int).Value = std["width"];
db.command.Parameters.Add("@thick", System.Data.SqlDbType.Int).Value = std["thick"];
db.command.Parameters.Add("@numpos", System.Data.SqlDbType.Int).Value = numpos;
db.command.Parameters.Add("@part", System.Data.SqlDbType.VarChar).Value = std["modelpart"];
db.command.Parameters.Add("@price", System.Data.SqlDbType.Decimal).Value = std["price"];
db.command.Parameters.Add("@qustore", System.Data.SqlDbType.Decimal).Value = Convert.ToDecimal(Useful.GetDouble(std["qustore"])*(1+waste/100));
db.command.Parameters.Add("@weight", System.Data.SqlDbType.Decimal).Value = Convert.ToDecimal(Useful.GetDouble(std["weight"]));
db.command.Parameters.Add("@idorderitem", System.Data.SqlDbType.Int).Value = std["idorderitem"];
db.command.ExecuteNonQuery();
db.CloseDB();
// к этому моменту мы создали и записали все, что можно!!!
}
}
}
#endregion проверка и создание расходных накладных
if (Atechnology.ecad.Calc.SystemScript.RunCalc.Errors.Count != 0){
Atechnology.ecad.Calc.SystemScript.RunCalc.Errors.ShowErrors("Ошибки");
Atechnology.ecad.Calc.SystemScript.RunCalc.Errors.Clear();
}else{
if (MessageBox.Show("Формирование накладных успешно завершено!\nПоказать накладные?", "Внимание", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes){
if (Atechnology.ecad.Report.AtReport.ReportsFromBase.Rows.Count == 0)
Atechnology.ecad.Report.AtReport.LoadReporsFromBase();
foreach (int id in StoreList){
Atechnology.ecad.Report.AtReport r = Atechnology.ecad.Report.AtReport.GetReport("Накладная");
ArrayList arl = new ArrayList();
// MessageBox.Show(id.ToString()+" \n "+id.GetType().ToString());
arl.Add(new Atechnology.ecad.Report.repVars("idstoredoc", id, id.GetType()));
r.vars = arl;
// данная строка показывает отчет
r.ShowReport();
// данная строка сразу печатает отчет
//r.PrintReport(1);
}
}
}
}catch(Exception e){
MessageBox.Show("Формирование накладных:\n"+
e.Message+"\n\n"+e.TargetSite.ToString()+" \n"+e.StackTrace + "\n" +
"Контрольная точка - " + err.ToString(),
"Ошибка скрипта документа накладных");
}
}
public void LoadData(){
// данная таблица нужна для того, чтобы отследить каких документов оптимизаций не сделано по пр заданию
if(!ds.Tables.Contains("docoper")){
table_docoper = new DataTable("docoper");
ds.Tables.Add(table_docoper);
db.command.CommandText = "select * from docoper where iddocappearance in (13, 5) and deleted is null";
db.adapter.Fill(table_docoper);
}else{
table_docoper = ds.Tables["docoper"];
table_docoper.Clear();
db.command.CommandText = "select * from docoper where iddocappearance in (13, 5) and deleted is null";
db.adapter.Fill(table_docoper);
}
// данная таблица нужна для того, чтобы определить, какой именно оптимизации не хватает
CommStr = "";
CommStr += " select * from optimdoc where deleted is null and idoptimdoc in ( ";
CommStr += " select idoptimdoc from optimdocpos where deleted is null and idmodelcalc in ( ";
CommStr += " select idmodelcalc from modelcalc where deleted is null and idorderitem in ( ";
CommStr += " select idorderitem from manufactdocpos where idmanufactdoc="+idmanufactdoc.ToString()+"))) ";
// MessageBox.Show(CommStr);
if(!ds.Tables.Contains("optimdoc")){
table_optimdoc = new DataTable("optimdoc");
ds.Tables.Add(table_optimdoc);
db.command.CommandText = CommStr;
db.adapter.Fill(table_optimdoc);
}else{
table_optimdoc = new DataTable("optimdoc");
db.command.CommandText = CommStr;
db.adapter.Fill(table_optimdoc);
table_optimdoc = ds.Tables["optimdoc"];
}
// данный блок загружает задания
if(!ds.Tables.Contains("equipmentdoc")){
table_equipmentdoc = new DataTable("equipmentdoc");
ds.Tables.Add(table_equipmentdoc);
db.command.CommandText = "select * from equipmentdoc where deleted is null and idmanufactdoc = "+idmanufactdoc.ToString();
db.adapter.Fill(table_equipmentdoc);
}else{
table_equipmentdoc = ds.Tables["equipmentdoc"];
db.command.CommandText = "select * from equipmentdoc where deleted is null and idmanufactdoc = "+idmanufactdoc.ToString();
db.adapter.Fill(table_equipmentdoc);
}
// данный блок загружает папки
if(!ds.Tables.Contains("storedocgroup")){
table_storedocgroup = new DataTable("storedocgroup");
ds.Tables.Add(table_storedocgroup);
db.command.CommandText = "select * from storedocgroup where deleted is null";
db.adapter.Fill(table_storedocgroup);
}else{
table_storedocgroup = ds.Tables["storedocgroup"];
db.command.CommandText = "select * from storedocgroup where deleted is null";
db.adapter.Fill(table_storedocgroup);
}
// данный блок загружает табличку docrelation для производственного
if(!ds.Tables.Contains("docrelation")){
table_docrelation = new DataTable("docrelation");
ds.Tables.Add(table_docrelation);
db.command.CommandText = "select * from docrelation where iddocappearanceparent = 2 and iddocappearancechild = 5 and deleted is null and idparentdoc = " +idmanufactdoc.ToString();
db.adapter.Fill(table_docrelation);
}else{
table_docrelation = ds.Tables["docrelation"];
table_docrelation.Clear();
db.command.CommandText = "select * from docrelation where iddocappearanceparent = 2 and iddocappearancechild = 5 and deleted is null and idparentdoc = " +idmanufactdoc.ToString();
db.adapter.Fill(table_docrelation);
}
// данный блок загружает все расходные накладные по темe
if(!ds.Tables.Contains("_storedoc")){
table_storedoc = new DataTable("_storedoc");
ds.Tables.Add(table_storedoc);
db.command.CommandText = "select * from storedoc where deleted is null";
db.adapter.Fill(table_storedoc);
}else{
table_storedoc = ds.Tables["_storedoc"];
db.command.CommandText = "select * from storedoc where deleted is null";
db.adapter.Fill(table_storedoc);
}
// // данный блок загружает все расходные материалы
// if(!ds.Tables.Contains("_storedocpos")){
// table_storedocpos = new DataTable("_storedocpos");
// ds.Tables.Add(table_storedocpos);
// db.command.CommandText = @"select mc.* from modelcalc mc join manufactdocpos mdp on mdp.idorderitem = mc.idorderitem where mdp.idmanufactdoc = "+idmanufactdoc.ToString();
// db.adapter.Fill(table_storedocpos);
// table_storedocpos = ds.Tables["_storedocpos"];
// }else{
// table_storedocpos = ds.Tables["_storedocpos"];
// }
}
public bool TestDubl(int iddocoper, DataRow [] _tt){
foreach(DataRow fr in _tt){
DataRow [] rr = table_storedoc.Select("deleted is null and idstoredoc = "+fr["idchilddoc"].ToString());
//MessageBox.Show(rr.Length.ToString()+" это мы внутри функции... ");
int iii = 0;
//MessageBox.Show(_tt.Length.ToString()+" это длина docrel "+iddoc.ToString()+" а это iddoc" );
foreach(DataRow rdd in rr){
iii++;
//MessageBox.Show(" это мы внутри цикла... "+iii.ToString());
if ( rdd["iddocoper"].ToString() == iddocoper.ToString()){
string docopername = table_docoper.Select("iddocoper = "+iddocoper.ToString())[0]["name"].ToString();
Atechnology.ecad.Calc.SystemScript.RunCalc.Errors.Add("Для данного произв. задания уже существует тип расходной накладной: "+docopername, "Накл.: "+rdd["name"].ToString());
//MessageBox.Show("Для данного производственного задания уже существует такой тип расходной накладной");
return false;
}
}
}
return true;
}
}
}