public virtual DataSet1.DataTable1DataTable GetData()
{
this.Adapter.SelectCommand = this.CommandCollection[0];
DataSet1.DataTable1DataTable dataTable = new DataSet1.DataTable1DataTable();
this.Adapter.Fill(dataTable);
return(dataTable);
}
public virtual DataSet1.DataTable1DataTable GetData(int id)
{
this.Adapter.SelectCommand = this.CommandCollection[0];
this.Adapter.SelectCommand.Parameters[0].Value = ((int)(id));
DataSet1.DataTable1DataTable dataTable = new DataSet1.DataTable1DataTable();
this.Adapter.Fill(dataTable);
return(dataTable);
}
public void Run()
{
// 出力フォルダー作成
var outputFolder = System.IO.Path.Combine(_outputParentFolder, System.IO.Path.GetFileName(_targetFolder));
if (!System.IO.Directory.Exists(outputFolder))
{
System.IO.Directory.CreateDirectory(outputFolder);
}
else
{
foreach (var file in System.IO.Directory.GetFiles(outputFolder))
{
try
{
var fileInfo = new System.IO.FileInfo(file);
fileInfo.Delete();
}
catch (Exception)
{
}
}
}
// ファイルを読み込む
List <(string ColA, string ColB)> allLines = new List <(string ColA, string ColB)>();
foreach (var file in System.IO.Directory.GetFiles(_targetFolder, "*.xls*", System.IO.SearchOption.TopDirectoryOnly))
{
allLines.AddRange(ReadFile(file));
}
// A列の重複を削除
var targetLines = new List <(string ColA, string ColB)>();
foreach (var group in allLines.GroupBy(x => x.ColA))
{
targetLines.Add(group.First());
}
// ①のファイル用に加工する
DataSet1.DataTable1DataTable dt1 = EditFor1(targetLines);
// ②のファイル用に加工する
DataSet1.DataTable2DataTable dt2 = EditFor2(targetLines);
// ファイル保存
SaveFile(Path.Combine(outputFolder, targetLines.Count.ToString() + ".xlsx"), targetLines);
// ①のファイルを保存する
SaveTsvFile(Path.Combine(outputFolder, "①.tsv"), dt1);
// ②のファイルを保存する
SaveTsvFile(Path.Combine(outputFolder, "②.tsv"), dt2);
}
public virtual int Fill(DataSet1.DataTable1DataTable dataTable)
{
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((this.ClearBeforeFill == true))
{
dataTable.Clear();
}
int returnValue = this.Adapter.Fill(dataTable);
return(returnValue);
}
public virtual int Fill(DataSet1.DataTable1DataTable dataTable, int id)
{
this.Adapter.SelectCommand = this.CommandCollection[0];
this.Adapter.SelectCommand.Parameters[0].Value = ((int)(id));
if ((this.ClearBeforeFill == true))
{
dataTable.Clear();
}
int returnValue = this.Adapter.Fill(dataTable);
return(returnValue);
}
private void btnAfisare_Click(object sender, EventArgs e)
{
if (rbnLivrate.Checked == false &&
rbnNelivrate.Checked == false)
{
return;
}
if (rbnLivrate.Checked == true)
{
try
{
DataSet1TableAdapters.DataTable1TableAdapter adapter = new DataSet1TableAdapters.DataTable1TableAdapter();
DataSet1.DataTable1DataTable table = new DataSet1.DataTable1DataTable();
adapter.Fill(table);
ReportDataSource source = new ReportDataSource("ComenziFurnizori", (DataTable)table);
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(source);
reportViewer1.LocalReport.Refresh();
reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MetroFramework.MetroMessageBox.Show(this, "A aparut o problema la incarcarea datelor.\n" + ex, "Atentie!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
if (rbnNelivrate.Checked == true)
{
try
{
DataSet1TableAdapters.DataTable1TableAdapter adapter = new DataSet1TableAdapters.DataTable1TableAdapter();
DataSet1.DataTable1DataTable table = new DataSet1.DataTable1DataTable();
adapter.FillByNoDelivery(table);
ReportDataSource source = new ReportDataSource("ComenziFurnizori", (DataTable)table);
reportViewer1.LocalReport.DataSources.Clear();
reportViewer1.LocalReport.DataSources.Add(source);
reportViewer1.LocalReport.Refresh();
reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MetroFramework.MetroMessageBox.Show(this, "A aparut o problema la incarcarea datelor.\n" + ex, "Atentie!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
public Invoice(string invoiceNumber)
{
InitializeComponent();
CenterToScreen();
var adapter = new DataSet1TableAdapters.DataTable1TableAdapter();
adapter.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["SyncDB"].ToString();
var table = new DataSet1.DataTable1DataTable();
adapter.FillByInvoiceNumber(table, invoiceNumber);
var newDataSource = new ReportDataSource("DataSet1", (DataTable)table);
this.reportViewer1.LocalReport.DataSources.Clear();
this.reportViewer1.LocalReport.DataSources.Add(newDataSource);
ReportParameter[] parameters = new ReportParameter[13];
using (var context = new ManagementDbEntities())
{
double totalAmount;
totalAmount = 0;
var StockOutRecords = context.StockOutRecords.Where(r => r.InvoiceNumber == invoiceNumber).ToList();
foreach (var record in StockOutRecords)
{
totalAmount += record.ProductTotalAmount.GetValueOrDefault();
}
var firstRecord = StockOutRecords.FirstOrDefault();
var currentUser = context.Users.FirstOrDefault();
parameters[0] = new ReportParameter("CustomerName", firstRecord.CustomerName);
parameters[1] = new ReportParameter("CustomerContactPerson", firstRecord.CustomerContactPerson);
parameters[2] = new ReportParameter("CustomerContactNumber", firstRecord.CustomerContact);
parameters[3] = new ReportParameter("CustomerAddress", firstRecord.CustomerAddress);
parameters[4] = new ReportParameter("InvoiceDate", firstRecord.Date.GetValueOrDefault().ToString("yyyy-MM-dd"));
parameters[5] = new ReportParameter("Amount", totalAmount.ToString());
parameters[6] = new ReportParameter("InvoiceNumber", firstRecord.InvoiceNumber);
parameters[7] = new ReportParameter("CapitalAmount", GetCapitalAmount(totalAmount));
parameters[8] = new ReportParameter("CompanyName", currentUser.CompanyName);
parameters[9] = new ReportParameter("CompanyAddress", currentUser.CompanyAddress);
parameters[10] = new ReportParameter("CompanyTel", currentUser.CompanyContact);
parameters[11] = new ReportParameter("CompanyFax", currentUser.CompanyFax);
parameters[12] = new ReportParameter("MadeBy", currentUser.InvoiceCreator);
}
this.reportViewer1.LocalReport.SetParameters(parameters);
this.reportViewer1.LocalReport.Refresh();
this.reportViewer1.RefreshReport();
}
private void crystalReportViewer1_Load(object sender, EventArgs e)
{
if (_RepNo == 1)
{
DataSet ds = new DataSet();
DataSet1.DataTable1DataTable dataTable1 = new DataSet1.DataTable1DataTable();
DataSet1.DataTable2DataTable dataTable2 = new DataSet1.DataTable2DataTable();
ExecRepository repo = new ExecRepository();
ExecQueryParameters QueryParms = new ExecQueryParameters();
core = new Util();
//================================================================
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Description", typeof(string)); // 0
//===============================================================================
dt.Columns.Add("01", typeof(decimal)); // 1
dt.Columns[1].DefaultValue = 0.0;
dt.Columns.Add("02", typeof(decimal)); // 2
dt.Columns[2].DefaultValue = 0.0;
dt.Columns.Add("03", typeof(decimal)); // 3
dt.Columns[3].DefaultValue = 0.0;
dt.Columns.Add("04", typeof(decimal)); // 4
dt.Columns[4].DefaultValue = 0.0;
dt.Columns.Add("05", typeof(decimal)); // 5
dt.Columns[5].DefaultValue = 0.0;
dt.Columns.Add("06", typeof(decimal)); // 6
dt.Columns[6].DefaultValue = 0.0;
dt.Columns.Add("07", typeof(decimal)); // 7
dt.Columns[7].DefaultValue = 0.0;
dt.Columns.Add("08", typeof(decimal)); // 8
dt.Columns[8].DefaultValue = 0.0;
dt.Columns.Add("09", typeof(decimal)); // 9
dt.Columns[9].DefaultValue = 0.0;
dt.Columns.Add("10", typeof(decimal)); // 10
dt.Columns[10].DefaultValue = 0.0;
dt.Columns.Add("11", typeof(decimal)); // 11
dt.Columns[11].DefaultValue = 0.0;
dt.Columns.Add("12", typeof(decimal)); // 12
dt.Columns[12].DefaultValue = 0.0;
dt.Columns.Add("13", typeof(int)); // 13
dt.Columns[13].DefaultValue = 0;
using (var context = new TTI2Entities())
{
QueryParms.ToDate = DateTime.Now;
QueryParms.FromDate = QueryParms.ToDate.AddDays(-1 * QueryParms.ToDate.DayOfYear + 1);
var YarnProd = repo.ExecYarnProduction(QueryParms).GroupBy(x => x.YarnOP_DatePacked.Value.Month);
if (YarnProd.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "Spinning - Yarn Production";
foreach (var Mth in YarnProd)
{
var MthKey = Mth.FirstOrDefault().YarnOP_DatePacked.Value.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Mth.Sum(x => (decimal?)x.YarnOP_NettWeight) ?? 0.0M;
}
}
Row[13] = 1;
dt.Rows.Add(Row);
}
var GreigeProd = repo.ExecGreigeProduction(QueryParms).GroupBy(x => x.GreigeP_PDate.Value.Month);
if (GreigeProd.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "Knitting - Greige Production";
foreach (var Mth in GreigeProd)
{
var MthKey = Mth.FirstOrDefault().GreigeP_PDate.Value.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Mth.Sum(x => (decimal?)x.GreigeP_weight) ?? 0.0M;
}
}
Row[13] = 2;
dt.Rows.Add(Row);
}
var DyedNotComplete = repo.ExecDyeNotFinshed(QueryParms).GroupBy(x => x.TLDYET_Date.Month);
if (DyedNotComplete.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "Dyeing - Fabric Dyed";
foreach (var Mth in DyedNotComplete)
{
var MthKey = Mth.FirstOrDefault().TLDYET_Date.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Mth.Sum(x => (decimal?)x.TLDYET_BatchWeight) ?? 0.0M;
}
}
Row[13] = 3;
dt.Rows.Add(Row);
}
var DyedIntoQuarantine = repo.ExecDyeIntoQuarantine(QueryParms).GroupBy(x => x.DYEBO_ApprovalDate.Value.Month);
if (DyedIntoQuarantine.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "Dyeing - Fabric Into Quarantine";
foreach (var Mth in DyedIntoQuarantine)
{
var MthKey = Mth.FirstOrDefault().DYEBO_ApprovalDate.Value.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Mth.Sum(x => (decimal?)x.DYEBO_Nett) ?? 0.0M;
}
}
Row[13] = 4;
dt.Rows.Add(Row);
}
var CutProd = repo.ExecIntoPanelStore(QueryParms).GroupBy(x => x.TLCUTSHRD_PanelDate.Value.Month);
if (CutProd.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "Cutting - Panels into Store";
foreach (var Mth in CutProd)
{
var MthKey = Mth.FirstOrDefault().TLCUTSHRD_PanelDate.Value.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Mth.Sum(x => (decimal?)x.TLCUTSHRD_BoxUnits) ?? 0.0M;
}
}
Row[13] = 5;
dt.Rows.Add(Row);
}
var WorkComplete = repo.ExecWorkCompleted(QueryParms);
if (WorkComplete.Count() != 0)
{
DataRow Row = dt.NewRow();
Row[0] = "CMT - Work Completed";
var Records = (from LI in context.TLCMT_LineIssue
join WC in context.TLCMT_CompletedWork on LI.TLCMTLI_Pk equals WC.TLCMTWC_LineIssue_FK
where LI.TLCMTLI_WorkCompleted && LI.TLCMTLI_WorkCompletedDate >= QueryParms.FromDate && LI.TLCMTLI_WorkCompletedDate <= QueryParms.ToDate
select new { LI.TLCMTLI_WorkCompletedDate, WC.TLCMTWC_Qty }).GroupBy(x => x.TLCMTLI_WorkCompletedDate.Value.Month);
foreach (var Group in Records)
{
var MthKey = Group.FirstOrDefault().TLCMTLI_WorkCompletedDate.Value.Month.ToString().PadLeft(2, '0');
var ColIndex = dt.Columns.IndexOf(MthKey);
if (ColIndex != 0)
{
Row[ColIndex] = Row.Field <decimal>(ColIndex) + Group.Sum(x => (decimal?)x.TLCMTWC_Qty) ?? 0.0M;
}
}
Row[13] = 6;
dt.Rows.Add(Row);
foreach (DataRow DRow in dt.Rows)
{
DataSet1.DataTable1Row nr = dataTable1.NewDataTable1Row();
nr.Description = DRow.Field <String>(0);
nr.Jan = DRow.Field <decimal>(1);
nr.Feb = DRow.Field <decimal>(2);
nr.Mar = DRow.Field <decimal>(3);
nr.Apr = DRow.Field <decimal>(4);
nr.May = DRow.Field <decimal>(5);
nr.Jun = DRow.Field <decimal>(6);
nr.Jul = DRow.Field <decimal>(7);
nr.Aug = DRow.Field <decimal>(8);
nr.Sep = DRow.Field <decimal>(9);
nr.Oct = DRow.Field <decimal>(10);
nr.Nov = DRow.Field <decimal>(11);
nr.Dec = DRow.Field <decimal>(12);
nr.SortOrder = DRow.Field <int>(13);
nr.Pk = 1;
dataTable1.AddDataTable1Row(nr);
}
dt.Rows.Clear();
}
}
DataSet1.DataTable2Row hnr = dataTable2.NewDataTable2Row();
hnr.Pk = 1;
hnr.FromDate = QueryParms.FromDate;
hnr.ToDate = QueryParms.ToDate;
hnr.Title = "Company Production By Department";
dataTable2.AddDataTable2Row(hnr);
if (dataTable1.Rows.Count == 0)
{
DataSet1.DataTable1Row nr = dataTable1.NewDataTable1Row();
nr.ErrorLog = "No record found peratining to selection made";
dataTable1.AddDataTable1Row(nr);
}
ds.Tables.Add(dataTable1);
ds.Tables.Add(dataTable2);
Production Prod = new Production();
Prod.SetDataSource(ds);
crystalReportViewer1.ReportSource = Prod;
}
crystalReportViewer1.Refresh();
}
private void crystalReportViewer1_Load(object sender, EventArgs e)
{
if (_RepNo == 1)
{
DataSet ds = new DataSet();
DataSet1.DataTable1DataTable Table1 = new DataSet1.DataTable1DataTable();
DataSet1.DataTable2DataTable Table2 = new DataSet1.DataTable2DataTable();
List <TLSEC_UserAccess> Users = null;
using (var context = new TTI2Entities())
{
if (_RepOpts.AllRecords)
{
Users = context.TLSEC_UserAccess.OrderBy(x => x.TLSECUA_UserName).ToList();
}
else if (_RepOpts.ActiveRecords)
{
Users = context.TLSEC_UserAccess.Where(x => !x.TLSECUA_Discontinued && x.TLSECUA_Pk == 57).OrderBy(x => x.TLSECUA_UserName).ToList();
}
else
{
Users = context.TLSEC_UserAccess.Where(x => x.TLSECUA_Discontinued).OrderBy(x => x.TLSECUA_UserName).ToList();
}
int Cnt = 0;
foreach (var User in Users)
{
DataSet1.DataTable1Row t1r = Table1.NewDataTable1Row();
t1r.Pk = ++Cnt;
t1r.UserName = User.TLSECUA_UserName;
t1r.SuperUser = User.TLSECUA_SuperUser;
t1r.Discontinued = User.TLSECUA_Discontinued;
if (User.TLSECUA_Discontinued)
{
t1r.DisDate = (DateTime)User.TLSECUA_DisDate;
}
t1r.ConfirmedPassword = User.TLSECUA_ConfirmedPassword;
Table1.AddDataTable1Row(t1r);
var UserSections = context.TLSEC_UserSections.Where(x => x.TLSECDEP_User_FK == User.TLSECUA_Pk).ToList();
foreach (var UserSection in UserSections)
{
DataSet1.DataTable2Row t2r = Table2.NewDataTable2Row();
t2r.Pk = Cnt;
var UserSec = context.TLSEC_Sections.Find(UserSection.TLSECDEP_Section_FK);
if (UserSec != null)
{
t2r.UserSection = UserSec.TLSECSect_Description;
}
t2r.Department = context.TLSEC_Departments.Find(UserSection.TLSECDEP_Department_FK).TLSECDT_Description;
Table2.AddDataTable2Row(t2r);
}
}
if (Table1.Rows.Count == 0)
{
DataSet1.DataTable1Row t1r = Table1.NewDataTable1Row();
t1r.Pk = 1;
t1r.NoRecords = "No Records found for selection made";
Table1.AddDataTable1Row(t1r);
DataSet1.DataTable2Row t2r = Table2.NewDataTable2Row();
t2r.Pk = 1;
Table2.AddDataTable2Row(t2r);
}
ds.Tables.Add(Table1);
ds.Tables.Add(Table2);
UserAccess UserA = new UserAccess();
UserA.SetDataSource(ds);
crystalReportViewer1.ReportSource = UserA;
}
}
crystalReportViewer1.Refresh();
}
